SQLを使いこなしたい！虎の巻 #2 ~SQL操作入門編~

簡単なSQL文だけでも使えるようになりたいです〜！！！！！

#PostgreSQL

#SQL

たいがー

2020.10.12

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

寒すぎて最近あたたかいココアを解禁しました、新卒エンジニアのたいがーです?

前回、Docker環境下でPostgreSQLを使えるように環境を構築しました。今回はその続きですので、前回のブログをご覧になっていない方は是非一緒に環境を構築してみてください！

今回やってみること

こちら3点をやっていきます。

新しいデータベースを作成する
新しいテーブルを作成する
行を挿入 / 更新 / 削除 / 整列 / 範囲を指定する

今回は、私の"家探しの条件"をDBで作っていきたいと思います。

新しいデータベースを作成する

それではデータベースを作成してみます。

- 1週目に作成したコンテナにアクセス -
$ docker exec -it posgres bash
- データベースユーザー「user1」を作成する -
# createuser -U postgres user1
- データベースを作成する -
# createdb -U postgres -O user1 -E UTF8 --locale=C -T template0 howtosearchhouse
- 作成されたことを確認 -
#  psql -U postgres -l
- howtosearchhouseというデータベースが作成されているか確認する -
- 作成したデータベースユーザーで、作成したデータベースにアクセスする -
# psql -U user1 howtosearchhouse

これでデータベースが作成できました。続いて、テーブルを作成していきます。

新しいテーブルを作成する

今回はSQLファイルをローカルに作成し、コンテナにコピーします。まずはローカルにファイルを作成します。

CREATE TABLE conditions(
  優先順位 INTEGER
, 番号 INTEGER PRIMARY KEY
, 優先事項 VARCHAR(50) NOT NULL unique
, ジャンル VARCHAR(30)
);

CREATE TABLE genre(
  番号 INTEGER PRIMARY KEY
, ジャンル VARCHAR(30)
);

次にコンテナ名/IDを調べ、そのコンテナにコピーします。

- コンテナ名かIDを調べる -
$ docker ps
- そのコンテナにローカルからコピーする - 
$ docker cp ~/create-table.sql [コンテナ名/IDのどちらか]:create-table.sql
- コンテナに入る -
$ docker exec -it posgres bash
- データベースにアクセスする -
# psql -U user1 howtosearchhouse
- SQLファイルを実行する -
howtosearchhouse=> \i create-table.sql
- DBが作成されているか確認する -
howtosearchhouse=> \dt
- 定義したテーブルが作成されているか確認する -

これでテーブルを作成できました。続いてテーブルを変更していきます。

行を挿入 / 更新 / 削除 / 整列 / 範囲を指定する

新しい行を挿入する

まずは、insert文を使って、新しい行を挿入していきます。

howtosearchhouse=> insert into conditions(番号, 優先事項, ジャンル) values(101, 'バストイレ別', '水回り');

一度、テーブルの全ての行を表示させてみます。

howtosearchhouse=> select * from conditions;

表示結果は、こちらです。

 優先順位 | 番号  |      優先事項    |   ジャンル   
--------+------+-----------------+--------------
        |  101 | バストイレ別       | 水回り

次に、複数行を追加してみます。

howtosearchhouse=> insert into conditions(番号, 優先事項, ジャンル)
howtosearchhouse-> values(102, '病院に近い', '近所')
howtosearchhouse-> ,(103, '1階以外', 'セキュリティ')
howtosearchhouse-> ,(104, '飲食店が隣じゃない', '虫')
howtosearchhouse-> ,(105, 'コンビニが近い', '近所');

今度は表示させたい行を指定し、表示させてみます。

howtosearchhouse=> select ジャンル, 優先事項 from conditions;

     ジャンル   |      優先事項      
--------------+--------------------
 水回り         | バストイレ別
 近所          | 病院に近い
 セキュリティ     | 1階以外
 虫            | 飲食店が隣じゃない
 近所          | コンビニが近い
(5 rows)

さらに、続いてジャンルだけを絞って表示します。

howtosearchhouse=> select ジャンル, 優先事項 from conditions where ジャンル = '近所';

表示結果は、こちらです。

 ジャンル   |    優先事項    
----------+----------------
 近所     | 病院に近い
 近所     | コンビニが近い
(2 rows)

続いて、テーブルを更新していきます。

行を更新する

続いてupdate句を使い、先ほどのDBの中で優先順位をつけていきます。。

howtosearchhouse=> update conditions set 優先順位=1 where 番号 = 101;
UPDATE 1
howtosearchhouse=> update conditions set 優先順位=2 where 番号= 103;
UPDATE 1
howtosearchhouse=> update conditions set 優先順位=3 where 番号=105;
UPDATE 1
howtosearchhouse=> update conditions set 優先順位=4 where 番号=102;
UPDATE 1
howtosearchhouse=> update conditions set 優先順位=5 where 番号=104;
UPDATE 1
howtosearchhouse=> select * from conditions;

すると、このような結果になります。

 優先順位 | 番号 |      優先事項      |   ジャンル   
----------+------+--------------------+--------------
        1 |  101 | バストイレ別       | 水回り
        2 |  103 | 1階以外         | セキュリティ
        3 |  105 | コンビニが近い     | 近所
        4 |  102 | 病院に近い        | 近所
        5 |  104 | 飲食店が隣じゃない | 虫
(5 rows)

次に、削除を試してみます。

行を削除する

行を指定し、削除していきます。

howtosearchhouse=> delete from conditions where 番号 = 104;

実行結果はこちらです。

 優先順位 | 番号 |      優先事項      |   ジャンル   
----------+------+--------------------+--------------
        1 |  101 | バストイレ別       | 水回り
        2 |  103 | 1階以外         | セキュリティ
        3 |  105 | コンビニが近い     | 近所
        4 |  102 | 病院に近い        | 近所
(4 rows)

行を整列する

続いて、番号順に並び替えてみます。

howtosearchhouse=> select * from conditions order by 番号;

実行結果はこちらです。

 優先順位 | 番号 |    優先事項    |   ジャンル   
----------+------+----------------+--------------
        1 |  101 | バストイレ別   | 水回り
        4 |  102 | 病院に近い     | 近所
        2 |  103 | 1階以外        | セキュリティ
        3 |  105 | コンビニが近い | 近所
(4 rows)

範囲を指定する

最後に範囲を指定していきます。まずは先頭のn行だけ出力するのにlimit句を使います。

howtosearchhouse=> select ジャンル, 優先事項 from conditions order by 優先順位 limit 2;

実行結果はこちらです。

   ジャンル   |   優先事項   
--------------+--------------
 水回り       | バストイレ別
 セキュリティ | 1階以外
(2 rows)

次に、offset句を使い、先頭２行をスキップし表示してみます。

howtosearchhouse=> select ジャンル, 優先事項 from conditions order by 優先順位 offset 2;

実行結果はこちらです。

ジャンル |    優先事項    
----------+----------------
 近所     | コンビニが近い
 近所     | 病院に近い
 (2 rows)

最後にlimit句とoffset句を組み合わせ、真ん中2行を表示させてみます。

select ジャンル, 優先事項 from conditions order by 番号 limit 2 offset 1;

実行結果はこちらです。

 優先順位 | 番号 |  優先事項  |   ジャンル   
----------+------+------------+--------------
        4 |  102 | 病院に近い | 近所
        2 |  103 | 1階以外    | セキュリティ
(2 rows)